with base_table as (
--     select *
--     from (
    select date_time
         , waybill_no
         , agent_code
         , agent_name
         , project_name
         , site_code
         , site_name
         , max(is_void_flag)       as is_void_flag       --是否作废件 1是,0否
         , max(is_refund_flag)     as is_refund_flag     --是否退回件,1是,0否
         , max(is_sign_flag)       as is_sign_flag       --签收标识 1正常签收2退件签收0未签收
         , max(del_war_flag)       as del_war_flag       --派件标识,1代表派件未签收,0其他
         , max(work_order_join)    as work_order_join    --客诉标识,1代表客诉0代表非客诉
         , max(arbitration_join)   as arbitration_join   --丢失标识 ,1代表丢失,0代表未丢失
         , max(express_join)       as express_join       --破损标识 1,代表破损,0未破损
         , max(7d_update_flag)     as 7d_update_flag     --超过7天未跟新 1 是 0 否
         , max(instore_flag)       as instore_flag       --1代表入库操作,0代表未入库
         , max(diff_day)           as diff_day           --时效时间差
         , max(need_city_t_timely) as need_city_t_timely --时效天数
--                   , row_number()
--                      over (partition by waybill_no ,agent_code,project_name,date_time order by scan_time desc ) as rn
    from jms_dm.dm_mng_customer_daliy_center_waybill_detail_dt
    where dt >= date_add('{{ execution_date |  cst_ds }}', -6)
      and dt <= '{{ execution_date |  cst_ds }}'
    group by date_time
           , waybill_no
           , agent_code
           , agent_name
           , project_name
           , site_code
           , site_name
--          ) a
--     where a.rn = 1
),
     result_table as (
         select date_time                                                                   as date_time       --日期
              , agent_code                                                                  as agent_code      --代理区编码
              , agent_name                                                                  as agent_name      --代理区名称
              , project_name                                                                as project_name
              , count(waybill_no)                                                           as way_total       --流入量
              , sum(is_void_flag)                                                           as void_num        --取消量
              , sum(if(is_refund_flag <> 1, 1, 0))                                          as need_soft_num   --应妥投量 流入量-退件量 是否退回件,1是,0否
              , sum(
                 if(is_refund_flag <> 1 and (is_sign_flag <> 0 or instore_flag = 1), 1, 0)) as actual_soft_num --实际妥投量
              , sum(if(!(is_sign_flag <> 0 or instore_flag = 1), 1, 0))                     as no_soft_num     --未妥投量
              , sum(if(is_sign_flag <> 0, 1, 0))                                            as sign_num        --签收量
              , sum(if(is_sign_flag = 0, 1, 0))                                             as transport_num   --快件在途量 未签收量
              , sum(del_war_flag)                                                           as del_war_num     --派件未签收量
              , sum(work_order_join)                                                        as work_order_num  --客诉量
              , sum(arbitration_join)                                                       as arbitration_num --丢失量
              , sum(express_join)                                                           as express_num     --破损量
              , sum(if(7d_update_flag = 1 and is_sign_flag = 0, 1, 0))                      as 7d_update_num   --轨迹断更量
              , case
                    when max(need_city_t_timely) is null then null
                    else sum(if(diff_day is not null and diff_day - need_city_t_timely <= 0, 1, 0))
             end                                                                            as sign_in_num     --时效内签收量
              , case
                    when max(need_city_t_timely) is null then null
                    else sum(if(diff_day is not null and diff_day - need_city_t_timely > 0, 1, 0))
             end                                                                            as sign_out_num    --超时效签收量
              , case
                    when max(need_city_t_timely) is null then null
                    else sum(if(diff_day is null and diff_day - need_city_t_timely <= 0, 1, 0))
             end                                                                            as nosign_in_num   --时效内未签收量
              , case
                    when max(need_city_t_timely) is null then null
                    else sum(if(diff_day is null and diff_day - need_city_t_timely > 0, 1, 0))
             end                                                                            as nosign_out_num  --超时效内未签收量
              , date_time                                                                   as dt
         from base_table
         group by date_time
                , agent_code
                , agent_name
                , project_name
     )
insert
overwrite
table
jms_dm.dm_mng_customer_daliy_agent_summary_dt
partition
(
dt
)
select 1                                 as type
     , date_time                                      --日期
     , agent_code                                     --代理区编码
     , agent_name                                     --代理区名称
     , project_name                                   --项目名称
     , way_total                                      --流入量
     , void_num                                       --取消量
     , need_soft_num                                  --应妥投量
     , actual_soft_num                                --实际妥投量
     , no_soft_num                                    --未妥投量
     , sign_num                                       --签收量
     , transport_num                                  --快件在途量
     , del_war_num                                    --派件中量
     , work_order_num                                 --客诉量
     , arbitration_num                                --丢失量
     , express_num                                    --破损量
     , 7d_update_num                                  --轨迹断更量
     , sign_in_num                                    --时效内签收量
     , sign_out_num                                   --超时效签收量
     , nosign_in_num                                  --时效内未签收量
     , nosign_out_num                                 --超时效内未签收量
     , round(sign_in_num / way_total, 6) as sign_rate --时效达成率
     , dt
from result_table
union all
select 2                                           as type
     , date_time                                   as date_time       --日期
     , agent_code                                  as agent_code      --代理区编码
     , agent_name                                  as agent_name      --代理区名称
     , ''                                          as project_name
     , sum(way_total)                              as way_total       --流入量
     , sum(void_num)                               as void_num        --取消量
     , sum(need_soft_num)                          as need_soft_num   --应妥投量
     , sum(actual_soft_num)                        as actual_soft_num --实际妥投量
     , sum(no_soft_num)                            as no_soft_num     --未妥投量
     , sum(sign_num)                               as sign_num        --签收量
     , sum(transport_num)                          as transport_num   --快件在途量
     , sum(del_war_num)                            as del_war_num     --派件中量
     , sum(work_order_num)                         as work_order_num  --客诉量
     , sum(arbitration_num)                        as arbitration_num --丢失量
     , sum(express_num)                            as express_num     --破损量
     , sum(7d_update_num)                          as 7d_update_num   --轨迹断更量
     , sum(sign_in_num)                            as sign_in_num     --时效内签收量
     , sum(sign_out_num)                           as sign_out_num    --超时效签收量
     , sum(nosign_in_num)                          as nosign_in_num   --时效内未签收量
     , sum(nosign_out_num)                         as nosign_out_num  --超时效内未签收量
     , round(sum(sign_in_num) / sum(way_total), 6) as sign_rate       --时效达成率
     , dt
from result_table
group by date_time  --日期
       , agent_code --代理区编码
       , agent_name --代理区名称
       , dt
    distribute by dt, pmod(hash(project_name), 1)